
import pandas as pd
from openpyxl import load_workbook
import time
from datetime import datetime

filename = 'info.xlsx'
datafile = 'data.xlsx'

# 保存员工信息
def saveEmp(data):
    df_new  = pd.DataFrame(data)
    with pd.ExcelWriter(filename, mode='a', engine='openpyxl',if_sheet_exists='overlay') as writer:
        df_new.to_excel(writer, index=False, header=False, startrow=len(pd.read_excel(filename))+1)

# 根据名称删除员工
def deleteEmp(value):
    df = pd.read_excel(filename,header=0)
    # print(df.head)
    df = df.drop(df[(df['姓名'] == value)].index)
    df.to_excel(filename, index=False)
    
def getEmp(key,value):
    print('111111',key,value)
    df = pd.read_excel(filename,header=0)
    # print(df.head)
    df = df[(df[key] == value)]
    return df.to_dict(orient='records')
# 查询所有员工
def allEmp():
    df = pd.read_excel(filename)
    # 显示前几行数据，确保正确读取
    print(df.head())
    all_employees = df.to_dict(orient='records')
    for employee in all_employees:
        print(employee)

def alldata():
    df = pd.read_excel(datafile)
    # 将数据转换为列表
    return df[['员工编号', '姓名', '打卡类别', '打卡时间']].apply(tuple, axis=1).tolist()

def savedata(data):
    df_new  = pd.DataFrame(data)
    with pd.ExcelWriter(datafile, mode='a', engine='openpyxl',if_sheet_exists='overlay') as writer:
        df_new.to_excel(writer, index=False, header=False, startrow=len(pd.read_excel(datafile))+1)
        
if __name__ == "__main__":
    # # 创建数据帧
    data = {'员工编号': ['a345'],
            '姓名': ['zs张三'],
            '打卡类别': ['上班打卡'],
            '打卡时间': [datetime.fromtimestamp(time.time()).strftime('%Y-%m-%d %H:%M:%S')]}

    # saveEmp(data)
    # deleteEmp("zs")
    # allEmp()
    savedata(data)
    alldata()
